﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LPY.LeHealth.DTO;
using System.Data;
using System.Data.SqlClient;
using LPY.LeHealth.Model;
namespace LPY.LeHealth.DAL
{
    using ServiceStack.OrmLite;
    using LPY.LeHealth.Model;
    using System.Configuration;
    public  class HospitalAdminDal:BaseDAL
    {
        
        /// <summary>
        /// 医院管理员登陆
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public HospitalAdmin Get(string name, string pwd)
        { 
            using(var cnn=DbConnection)
            {
                var sql = cnn.From<HospitalAdmin>()
                    .Where(m=>m.Uname==name&&m.Pwd==pwd);
                var rtv = cnn.Single(sql);
                return rtv;
            }
        }

        /// <summary>
        /// 医院获取医院管理员信息
        /// </summary>
        /// <returns></returns>
        public List<HospitalAdmin> GetHospitalAdmin(int hospitalid)
        {
                string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
                string sql = "select t_HospitalAdmin.*,t_Hospital.Name from t_HospitalAdmin join t_Hospital on t_HospitalAdmin.HospitalId=t_Hospital.Id where t_HospitalAdmin.HospitalId='"+hospitalid+"'";
                SqlConnection conn = new SqlConnection(constring);
                SqlCommand command = new SqlCommand(sql,conn);
                conn.Open();
                SqlDataReader reader = command.ExecuteReader();
                List<HospitalAdmin> list = new List<HospitalAdmin>();
                while (reader.Read())
                {
                    HospitalAdmin ha = new HospitalAdmin();
                    ha.Id =Convert.ToInt32( reader["Id"]);
                    ha.Uname = reader["Uname"].ToString();
                    ha.Hname = reader["Name"].ToString();
                    ha.Pwd = reader["Pwd"].ToString();
                    list.Add(ha);
                }
                return list;
               
            
        }
        /// <summary>
        /// 维护端获取医院管理员信息
        /// </summary>
        /// <returns></returns>
        public List<HospitalAdmin> GetHospitalAdmin()
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
            string sql = "select t_HospitalAdmin.*,t_Hospital.Name from t_HospitalAdmin join t_Hospital on t_HospitalAdmin.HospitalId=t_Hospital.Id";
            SqlConnection conn = new SqlConnection(constring);
            SqlCommand command = new SqlCommand(sql, conn);
            conn.Open();
            SqlDataReader reader = command.ExecuteReader();
            List<HospitalAdmin> list = new List<HospitalAdmin>();
            while (reader.Read())
            {
                HospitalAdmin ha = new HospitalAdmin();
                ha.Id = Convert.ToInt32(reader["Id"]);
                ha.Uname = reader["Uname"].ToString();
                ha.Hname = reader["Name"].ToString();
                ha.Pwd = reader["Pwd"].ToString();
                list.Add(ha);
            }
            return list;


        }
        /// <summary>
        /// 注册医院管理员
        /// </summary>
        /// <param name="hos"></param>
        /// <returns></returns>
        public bool Adduser(HospitalAdmin hos)
        {
            using (var cnn = DbConnection)
            {
                var sql = cnn.Insert<HospitalAdmin>(hos);
                return sql>0; 
            }
        }
        /// <summary>
        /// 根据ID删除医院管理员
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteUser(int id)
        {
            using (var cnn = DbConnection)
            {
                var result = cnn.DeleteById<HospitalAdmin>(id);
                return result > 0;
            }
        }
        /// <summary>
        /// 根据ID获取医院管理员
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public HospitalAdmin GetPhysicalItemById(int id)
        {
            using (var cnn = DbConnection)
            {
                return cnn.SingleById<HospitalAdmin>(id);
            }
        }
        /// <summary>
        /// 页面首次加载时显示的体检人员信息
        /// </summary>
        /// <returns></returns>
        public List<PhysicalPeople1>SelectOrder(int hospitalid)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using (SqlConnection conn = new SqlConnection(constring))
            {

                conn.Open();
                string sqlstr = "select a.Ordercode,a.CreateDate,a.SumPrice,a.BookerName,a.BookerPhone,case when a.State=0 then '待支付' when a.State=1 then '待预约' when a.State=2 then '待体检' else '体检完成' end as State,c.Name,c.Price,d.Name as YName,a.TpeopleAdd from  t_order a inner join t_orderdetail b on a.Ordercode=b.OrderId inner join t_combo c on b.ComboId=c.Id inner join t_userdto d on a.UserId=d.Id where c.HospitalId='" + hospitalid + "'";//定义订单查询SQL语句

                //创建SQL命令
                SqlCommand cmd = new SqlCommand(sqlstr, conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);//创建数据适配器对象

                DataTable table = new DataTable();//创建内存表对象

                adp.Fill(table);//创建内存对象

                List<PhysicalPeople1> people = new List<PhysicalPeople1>();//创建体检人员集合

                foreach (DataRow item in table.Rows)//
                {
                    PhysicalPeople1 peop = new PhysicalPeople1();//创建体检人员实体对象

                    peop.OrderId = item["Ordercode"].ToString();//获取预约单号

                    peop.Name = item["BookerName"].ToString();//获取体检人姓名

                    peop.Phone = item["BookerPhone"].ToString();//获取体检人电话

                    peop.AppointmentTime = Convert.ToDateTime(item["CreateDate"].ToString());//获取预约时间

                    //peop.Gender = Convert.ToInt32(item["Gender"]);//获取性别

                    peop.State = item["State"].ToString();

                    peop.ComBomName = item["Name"].ToString();

                    peop.ComPrice = Convert.ToDecimal(item["Price"]);

                    peop.SumPrice = Convert.ToDecimal(item["SumPrice"]);

                    peop.Yname = item["YName"].ToString();

                    peop.SfAdd = item["TpeopleAdd"].ToString();
                    people.Add(peop);

                }

                return people;//返回体检人员集合对象


            }//连接数据库

        }
        /// <summary>
        /// 根据预约时间和订单号查询体检人员信息
        /// </summary>
        /// <param name="OrderId"></param>
        /// <param name="strtime1"></param>
        /// <param name="strtime2"></param>
        /// <returns></returns>

        public List<PhysicalPeople1> Selectorder(string OrderId, string strtime1, string strtime2, int hospitalid)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using (SqlConnection conn = new SqlConnection(constring))//连接数据库
            {
                conn.Open();
                string sqlstr = "select a.Ordercode,a.CreateDate,a.BookerName,a.SumPrice,a.BookerPhone,case when a.State=0 then '待支付' when a.State=1 then '待预约' when a.State=2 then '待体检' else '体检完成' end as State,c.Name,c.Price,d.Name as YName,a.TpeopleAdd from  t_order a inner join t_orderdetail b on a.Ordercode=b.OrderId inner join t_combo c on b.ComboId=c.Id inner join t_userdto d on a.UserId=d.Id where a.Ordercode='" + OrderId + "' or a.CreateDate between '" + strtime1 + "' and '" + strtime2 + "' and c.HospitalId='" + hospitalid + "'";

                //创建SQL命令
                SqlCommand cmd = new SqlCommand(sqlstr, conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);//创建数据适配器对象

                DataTable table = new DataTable();//创建内存表对象

                adp.Fill(table);//创建内存对象

                List<PhysicalPeople1> people = new List<PhysicalPeople1>();//创建体检人员集合

                foreach (DataRow item in table.Rows)//
                {
                    PhysicalPeople1 peop = new PhysicalPeople1();//创建体检人员实体对象

                    peop.OrderId = item["Ordercode"].ToString();//获取预约单号

                    peop.Name = item["BookerName"].ToString();//获取体检人姓名

                    peop.Phone = item["BookerPhone"].ToString();//获取体检人电话

                    peop.AppointmentTime = Convert.ToDateTime(item["CreateDate"].ToString());//获取预约时间

                    //peop.Gender = Convert.ToInt32(item["Gender"]);//获取性别

                    peop.State = item["State"].ToString();

                    peop.ComBomName = item["Name"].ToString();

                    peop.ComPrice = Convert.ToDecimal(item["Price"]);

                    peop.SumPrice = Convert.ToDecimal(item["SumPrice"]);

                    peop.SfAdd = item["TpeopleAdd"].ToString();//获取体检人身份证号

                    peop.Yname = item["YName"].ToString();//获取预订人姓名
                    people.Add(peop);

                }

                return people;//返回体检人员集合对象
            }

        }

        /// <summary>
        /// 根据预约时间查询订单信息
        /// </summary>
        /// <param name="strtime1"></param>
        /// <param name="strtime2"></param>
        /// <returns></returns>
        public List<PhysicalPeople1> Selectorder(string strtime1, string strtime2,int HospitalId)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using (SqlConnection conn = new SqlConnection(constring))//连接数据库
            {
                conn.Open();
                string sqlstr = "select a.Ordercode,a.CreateDate,a.BookerName,a.SumPrice,a.BookerPhone,case when a.State=0 then '待支付' when a.State=1 then '待预约' when a.State=2 then '待体检' else '体检完成' end as State,c.Name,c.Price,d.Name as YName,a.TpeopleAdd from  t_order a inner join t_orderdetail b on a.Ordercode=b.OrderId inner join t_combo c on b.ComboId=c.Id inner join t_userdto d on a.UserId=d.Id where c.HospitalId='" + HospitalId + "' and a.CreateDate between '" + strtime1 + "' and '" + strtime2 + "'";

                //创建SQL命令
                SqlCommand cmd = new SqlCommand(sqlstr, conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);//创建数据适配器对象

                DataTable table = new DataTable();//创建内存表对象

                adp.Fill(table);//创建内存对象

                List<PhysicalPeople1> people = new List<PhysicalPeople1>();//创建体检人员集合

                foreach (DataRow item in table.Rows)//
                {
                    PhysicalPeople1 peop = new PhysicalPeople1();//创建体检人员实体对象

                    peop.OrderId = item["Ordercode"].ToString();//获取预约单号

                    peop.Name = item["BookerName"].ToString();//获取体检人姓名

                    peop.Phone = item["BookerPhone"].ToString();//获取体检人电话

                    peop.AppointmentTime = Convert.ToDateTime(item["CreateDate"].ToString());//获取预约时间

                    //peop.Gender = Convert.ToInt32(item["Gender"]);//获取性别

                    peop.State = item["State"].ToString();

                    peop.ComBomName = item["Name"].ToString();

                    peop.ComPrice = Convert.ToInt32(item["Price"]);

                    peop.SumPrice = Convert.ToDecimal(item["SumPrice"]);

                    peop.SfAdd = item["TpeopleAdd"].ToString();//获取体检人身份证号

                    peop.Yname = item["YName"].ToString();//获取预订人姓名
                    people.Add(peop);

                }

                return people;//返回体检人员集合对象
            }

        }
        /// <summary>
        /// 按订单编号查询相应的订单详情
        /// </summary>
        /// <param name="OrderId"></param>
        /// <returns></returns>
        public List<OrderDcom>ShowOrder(string OrderId)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using (SqlConnection conn = new SqlConnection(constring))
            {
                conn.Open();
                string sqlss = "select a.ComboId,a.Count,b.SumPrice,b.PreferentialPrice,b.CreateDate,b.BookerName,b.BookerPhone,c.Name,b.State from t_orderdetail a inner join t_order b on a.OrderId=b.Ordercode inner join t_combo c on a.ComboId=c.Id where a.OrderId='"+OrderId+"'";

                SqlCommand cmd = new SqlCommand(sqlss,conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);

                DataTable table = new DataTable();

                adp.Fill(table);

                List<OrderDcom> decom = new List<OrderDcom>();
                foreach (DataRow item in table.Rows)
                {
                    OrderDcom ordec = new OrderDcom();

                    ordec.BookerName = item["BookerName"].ToString();//获取预定人
                    ordec.SumPrice = Convert.ToDecimal(item["SumPrice"]);//获取订单总价
                    ordec.ShopCount = Convert.ToInt32(item["Count"]);//获取商品数量
                    ordec.PreferentialPrice = Convert.ToDecimal(item["PreferentialPrice"]);//获取订单号
                    ordec.PayState = Convert.ToInt32(item["State"]);//获取订单状态
                    ordec.DcomName = item["Name"].ToString();//获取套餐名称
                    ordec.BookerPhone = item["BookerPhone"].ToString();//获取预定人电话
                    ordec.CreateDate = Convert.ToDateTime(item["CreateDate"]);//获取订单日期
                    ordec.DcomId = Convert.ToInt32(item["ComboId"]);
                    decom.Add(ordec);
                }
                return decom;
            }


        }
        /// <summary>
        /// 根据订单号删除体检信息
        /// </summary>
        /// <param name="orderid"></param>
        /// <returns></returns>
        public int DelDetail(int?orderid)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using (SqlConnection conn = new SqlConnection(constring))
            {
                conn.Open();
                string sqlstr = "delete from t_order where Ordercode='" + orderid + "'";

                SqlCommand cmd = new SqlCommand(sqlstr, conn);

                int result = cmd.ExecuteNonQuery();
                return result;
            }
        }
        /// <summary>
        /// 获取所有订单状态
        /// </summary>
        /// <returns></returns>
        public List<Order>ShowOrderState()
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using (SqlConnection conn = new SqlConnection(constring))//连接数据库
            {
                conn.Open();
                string sqlstr = "select a.* from t_order a";

                //创建SQL命令
                SqlCommand cmd = new SqlCommand(sqlstr, conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);//创建数据适配器对象

                DataTable table = new DataTable();//创建内存表对象

                adp.Fill(table);//创建内存对象

                List<Order> people = new List<Order>();//创建体检人员集合

                foreach (DataRow item in table.Rows)//
                {
                    Order peop = new Order();//创建体检人员实体对象

                    peop.State = Convert.ToInt32(item["State"]);
                    peop.Ordercode = item["Ordercode"].ToString();
                    people.Add(peop);

                }

                return people;//返回体检人员集合对象
            }
        }
        /// <summary>
        /// 根据订单编号,获取订单状态
        /// </summary>
        /// <param name="OrderId"></param>
        /// <returns></returns>
        public Order ShowOrderState(string OrderId)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using (SqlConnection conn = new SqlConnection(constring))//连接数据库
            {
                conn.Open();
                string sqlstr = "select a.* from t_order a where a.Ordercode='"+OrderId+"'";

                //创建SQL命令
                SqlCommand cmd = new SqlCommand(sqlstr, conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);//创建数据适配器对象

                DataTable table = new DataTable();//创建内存表对象

                adp.Fill(table);//创建内存对象
                Order peop = new Order();//创建体检人员实体对象
                    peop.State = Convert.ToInt32(table.Rows[0]["State"]);
                    peop.Ordercode = table.Rows[0]["Ordercode"].ToString();

                return peop;//返回体检人员对象
            }
        }
        /// <summary>
        /// 根据订单编号,修改订单状态
        /// </summary>
        /// <param name="OrderId"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public bool UpdateState(string OrderId,int state)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串
            using(SqlConnection conn=new SqlConnection(constring))
            {
                string updaestr = string.Format("update t_order set State={0} where Ordercode='{1}'",state,OrderId);

                conn.Open();

                SqlCommand cmd=new SqlCommand(updaestr,conn);

                bool flag = cmd.ExecuteNonQuery()>0;

                return flag;
            }
            //OrderDAL orderDal = new OrderDAL();
            //return orderDal.UpdateState(OrderId,(int)state);
        }
        /// <summary>
        /// 根据登陆用户名和医院编号查找登录人的ID
        /// </summary>
        /// <param name="username"></param>
        /// <param name="hotpitalid"></param>
        /// <returns></returns>
        public int SelectUserid(string username,int hotpitalid)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using(SqlConnection conn=new SqlConnection(constring))
            {
                string sqlstr = "select a.HospitalId from t_hospitaladmin a where a.Uname='" + username + "' and a.HospitalId='" + hotpitalid + "'";

                conn.Open();
                SqlCommand cmd = new SqlCommand(sqlstr, conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);

                DataTable table = new DataTable();
                adp.Fill(table);

                int id = Convert.ToInt32(table.Rows[0]["Id"]);

                return id;
            }

        }
        /// <summary>
        /// 根据订单状态和日期查询订单信息
        /// </summary>
        /// <returns></returns>
        public List<PhysicalPeople1>SelectState(int?StateId,string strtime1,string strtime2,int HospitalId)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串
            using(SqlConnection conn=new SqlConnection(constring))
            {
                conn.Open();

                string select = "select a.Ordercode,a.CreateDate,a.BookerName,a.BookerPhone,case when a.State=0 then '待支付' when a.State=1 then '待预约' when a.State=2 then '待体检' else '体检完成' end as State,c.Name,c.Price,d.Name as YName,a.TpeopleAdd from  t_order a inner join t_orderdetail b on a.Ordercode=b.OrderId inner join t_combo c on b.ComboId=c.Id inner join t_userdto d on a.UserId=d.Id where a.State='" + StateId + "' and c.HospitalId='" + HospitalId + "' and  a.CreateDate between '" + strtime1 + "' and '" + strtime2 + "'";

                //创建SQL命令
                SqlCommand cmd = new SqlCommand(select, conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);//创建数据适配器对象

                DataTable table = new DataTable();//创建内存表对象

                adp.Fill(table);//创建内存对象

                List<PhysicalPeople1> people = new List<PhysicalPeople1>();//创建体检人员集合

                foreach (DataRow item in table.Rows)//
                {
                    PhysicalPeople1 peop = new PhysicalPeople1();//创建体检人员实体对象

                    peop.OrderId = item["Ordercode"].ToString();//获取预约单号

                    peop.Name = item["BookerName"].ToString();//获取体检人姓名

                    peop.Phone = item["BookerPhone"].ToString();//获取体检人电话

                    peop.AppointmentTime = Convert.ToDateTime(item["CreateDate"].ToString());//获取预约时间

                    //peop.Gender = Convert.ToInt32(item["Gender"]);//获取性别

                    peop.State = item["State"].ToString();

                    peop.ComBomName = item["Name"].ToString();

                    peop.ComPrice = Convert.ToDecimal(item["Price"]);
                    peop.SfAdd = item["TpeopleAdd"].ToString();//获取体检人身份证号

                    peop.Yname = item["YName"].ToString();//获取预订人姓名

                    people.Add(peop);

                }

                return people;//返回体检人员集合对象
            }
        }
        /// <summary>
        /// 根据订单状态查询单据信息
        /// </summary>
        /// <param name="StateId"></param>
        /// <returns></returns>
        public List<PhysicalPeople1>SelectStateId(int?StateId,int HospitalId)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串
            using (SqlConnection conn = new SqlConnection(constring))
            {

                conn.Open();

                string select = "select a.Ordercode,a.CreateDate,a.BookerName,a.BookerPhone,case when a.State=0 then '待支付' when a.State=1 then '待预约' when a.State=2 then '待体检' else '体检完成' end as State,c.Name,c.Price,d.Name as YName,a.TpeopleAdd from  t_order a inner join t_orderdetail b on a.Ordercode=b.OrderId inner join t_combo c on b.ComboId=c.Id inner join t_userdto d on a.UserId=d.Id where a.State='" + StateId + "' and c.HospitalId='" + HospitalId + "'";

                //创建SQL命令
                SqlCommand cmd = new SqlCommand(select, conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);//创建数据适配器对象

                DataTable table = new DataTable();//创建内存表对象

                adp.Fill(table);//创建内存对象

                List<PhysicalPeople1> people = new List<PhysicalPeople1>();//创建体检人员集合

                foreach (DataRow item in table.Rows)//
                {
                    PhysicalPeople1 peop = new PhysicalPeople1();//创建体检人员实体对象

                    peop.OrderId = item["Ordercode"].ToString();//获取预约单号

                    peop.Name = item["BookerName"].ToString();//获取体检人姓名

                    peop.Phone = item["BookerPhone"].ToString();//获取体检人电话

                    peop.AppointmentTime = Convert.ToDateTime(item["CreateDate"].ToString());//获取预约时间

                    //peop.Gender = Convert.ToInt32(item["Gender"]);//获取性别

                    peop.State = item["State"].ToString();

                    peop.ComBomName = item["Name"].ToString();

                    peop.ComPrice = Convert.ToDecimal(item["Price"]);

                    peop.SfAdd = item["TpeopleAdd"].ToString();//获取体检人身份证号

                    peop.Yname = item["YName"].ToString();//获取预订人姓名
                    people.Add(peop);

                }

                return people;//返回体检人员集合对象
            }

        }
        /// <summary>
        /// 绑定套餐
        /// </summary>
        /// <returns></returns>
        public List<OrderDcom>SelectDcom(int HospitalId)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串
            using (SqlConnection conn = new SqlConnection(constring))
            {
                conn.Open();

                string sqls = "select a.Id,a.Name from t_combo a where a.HospitalId='"+HospitalId+"'";
                SqlCommand cmd = new SqlCommand(sqls, conn);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                DataTable talbe = new DataTable();
                adp.Fill(talbe);
                List<OrderDcom> combo = new List<OrderDcom>();
                foreach(DataRow row in talbe.Rows)
                {
                    OrderDcom dcom = new OrderDcom();
                    dcom.DcomId = Convert.ToInt32(row["Id"]);

                    dcom.DcomName = row["Name"].ToString();

                    combo.Add(dcom);
                }
                return combo;
            }
        }
        /// <summary>
        /// 根据套餐ID显示订单信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<PhysicalPeople1>SelectComboId(int?id,int Hospitalid)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using (SqlConnection conn = new SqlConnection(constring))
            {

                conn.Open();
                string sqlstr = "select a.Ordercode,a.CreateDate,a.SumPrice,a.BookerName,a.BookerPhone,case when a.State=0 then '待支付' when a.State=1 then '待预约' when a.State=2 then '待体检' else '体检完成' end as State,c.Name,c.Price,d.Name as YName,a.TpeopleAdd from  t_order a inner join t_orderdetail b on a.Ordercode=b.OrderId inner join t_combo c on b.ComboId=c.Id inner join t_userdto d on a.UserId=d.Id where b.ComboId='" + id + "' and c.HospitalId='" + Hospitalid + "'";//定义订单查询SQL语句

                //创建SQL命令
                SqlCommand cmd = new SqlCommand(sqlstr, conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);//创建数据适配器对象

                DataTable table = new DataTable();//创建内存表对象

                adp.Fill(table);//创建内存对象

                List<PhysicalPeople1> people = new List<PhysicalPeople1>();//创建体检人员集合

                foreach (DataRow item in table.Rows)//
                {
                    PhysicalPeople1 peop = new PhysicalPeople1();//创建体检人员实体对象

                    peop.OrderId = item["Ordercode"].ToString();//获取预约单号

                    peop.Name = item["BookerName"].ToString();//获取体检人姓名

                    peop.Phone = item["BookerPhone"].ToString();//获取体检人电话

                    peop.AppointmentTime = Convert.ToDateTime(item["CreateDate"].ToString());//获取预约时间

                    //peop.Gender = Convert.ToInt32(item["Gender"]);//获取性别

                    peop.State = item["State"].ToString();

                    peop.ComBomName = item["Name"].ToString();

                    peop.ComPrice = Convert.ToDecimal(item["Price"]);

                    peop.SumPrice = Convert.ToDecimal(item["SumPrice"]);

                    peop.SfAdd = item["TpeopleAdd"].ToString();//获取体检人身份证号

                    peop.Yname = item["YName"].ToString();//获取预订人姓名
                    people.Add(peop);

                }

                return people;//返回体检人员集合对象


            }//连接数据库
        }
        /// <summary>
        /// 批量审批订单状态
        /// </summary>
        /// <param name="OrderId"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public bool PupdateState(string OrderId,int state)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串
            using (SqlConnection conn = new SqlConnection(constring))
            {
                string updaestr = string.Format("update t_order set State={0} where Ordercode='{1}'", state, OrderId);

                conn.Open();

                SqlCommand cmd = new SqlCommand(updaestr, conn);

                bool flag = cmd.ExecuteNonQuery()>0;

                return flag;
            }
        }

        public List<StateDemo>SelectState()
        {
            List<StateDemo> staed =new List<StateDemo>
            {
               new StateDemo{StateDemoId=4,StateName="请选择"},
                new StateDemo{StateDemoId=0,StateName="待支付"},
                new StateDemo{StateDemoId=1,StateName="待预约"},
                new StateDemo{StateDemoId=2,StateName="待体检"},
                new StateDemo{StateDemoId=3,StateName="体检完成"}
                 

            };

            return staed;
            
        }


    }
}
